package com.example.demo.excel;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;

/**
 * @author yongsheng.he
 * @describe excel工具类 提供读取和写入excel的功能
 * @date 2018/06/30 23:40
 */
public class ExcelUtil {

	/**
	 * 读取excel文件里面的内容
	 *
	 * @param file 文件
	 *
	 * @return
	 *
	 * @throws FileNotFoundException
	 * @throws IOException
	 */
	public static List<ExcelSheetPO> readExcel(File file) throws IOException {
		return readExcel(file, null, null);
	}

	/**
	 * 读取excel文件里面的内容
	 *
	 * @param file        文件
	 * @param rowCount    条数
	 * @param columnCount 列数
	 *
	 * @return
	 *
	 * @throws IOException
	 */
	public static List<ExcelSheetPO> readExcel(File file, Integer rowCount, Integer columnCount) throws IOException {
		return readExcel(new FileInputStream(file), file.getName(), rowCount, columnCount);
	}

	/**
	 * 读取excel文件里面的内容
	 *
	 * @param inputStream 文件流
	 * @param fileName    文件名
	 *
	 * @return
	 *
	 * @throws IOException
	 */
	public static List<ExcelSheetPO> readExcel(InputStream inputStream, String fileName) throws IOException {
		return readExcel(inputStream, fileName, null, null);
	}

	/**
	 * 读取excel文件里面的内容
	 *
	 * @param inputStream 文件流
	 * @param fileName    文件名
	 * @param rowCount    条数
	 * @param columnCount 列数
	 *
	 * @return
	 *
	 * @throws FileNotFoundException
	 * @throws IOException
	 */
	public static List<ExcelSheetPO> readExcel(InputStream inputStream, String fileName, Integer rowCount, Integer columnCount) throws IOException {

		Workbook wb = null;
		// 根据后缀名称判断excel的版本
		String extName = fileName.substring(fileName.lastIndexOf("."));
		if (".xls".equals(extName)) {
			wb = new HSSFWorkbook(inputStream);
		} else if (".xlsx".equals(extName)) {
			wb = new XSSFWorkbook(inputStream);
		} else {
			// 无效后缀名称，这里之能保证excel的后缀名称，不能保证文件类型正确，不过没关系，在创建Workbook的时候会校验文件格式
			throw new IllegalArgumentException("Invalid excel version");
		}

		return readExcel(wb, rowCount, columnCount);
	}

	//<------辅助的私有方法------>

	/**
	 * 读取excel文件里面的内容 支持日期，数字，字符，函数公式，布尔类型
	 *
	 * @param wb
	 * @param rowCount    条数
	 * @param columnCount 列数
	 *
	 * @return
	 */
	private static List<ExcelSheetPO> readExcel(Workbook wb, Integer rowCount, Integer columnCount) {
		// 开始读取数据
		List<ExcelSheetPO> sheetPOs = new ArrayList<>();
		// 解析sheet
		for (int i = 0; i < wb.getNumberOfSheets(); i++) {
			Sheet sheet = wb.getSheetAt(i);
			List<List<Object>> dataList = new ArrayList<>();
			ExcelSheetPO sheetPO = new ExcelSheetPO();
			sheetPO.setSheetName(sheet.getSheetName());
			sheetPO.setDataList(dataList);
			int readRowCount = 0;
			if (rowCount == null || rowCount > sheet.getPhysicalNumberOfRows()) {
				readRowCount = sheet.getPhysicalNumberOfRows();
			} else {
				readRowCount = rowCount;
			}
			// 解析sheet 的行
			for (int j = sheet.getFirstRowNum(); j < readRowCount; j++) {
				Row row = sheet.getRow(j);
				if (row == null) {
					continue;
				}
				if (row.getFirstCellNum() < 0) {
					continue;
				}
				int readColumnCount = 0;
				if (columnCount == null || columnCount > row.getLastCellNum()) {
					readColumnCount = (int) row.getLastCellNum();
				} else {
					readColumnCount = columnCount;
				}
				List<Object> rowValue = new LinkedList<Object>();
				// 解析sheet 的列
				for (int k = 0; k < readColumnCount; k++) {
					Cell cell = row.getCell(k);
					rowValue.add(getCellValue(wb, cell));
				}
				dataList.add(rowValue);
			}
			sheetPOs.add(sheetPO);
		}
		return sheetPOs;
	}

	/**
	 * 获取单元格的值
	 *
	 * @param wb
	 * @param cell
	 *
	 * @return
	 */
	private static Object getCellValue(Workbook wb, Cell cell) {
		Object columnValue = null;
		if (cell != null) {
			DecimalFormat df = new DecimalFormat("0");// 格式化 number
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 格式化日期字符串
			DecimalFormat nf = new DecimalFormat("0.00");// 格式化数字
			switch (cell.getCellType()) {
				case Cell.CELL_TYPE_STRING:
					columnValue = cell.getStringCellValue();
					break;
				case Cell.CELL_TYPE_NUMERIC:
					if ("@".equals(cell.getCellStyle().getDataFormatString())) {
						columnValue = df.format(cell.getNumericCellValue());
					} else if ("General".equals(cell.getCellStyle().getDataFormatString())) {
						columnValue = nf.format(cell.getNumericCellValue());
					} else {
						columnValue = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
					}
					break;
				case Cell.CELL_TYPE_BOOLEAN:
					columnValue = cell.getBooleanCellValue();
					break;
				case Cell.CELL_TYPE_BLANK:
					columnValue = "";
					break;
				case Cell.CELL_TYPE_FORMULA:
					// 格式单元格
					FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
					evaluator.evaluateFormulaCell(cell);
					CellValue cellValue = evaluator.evaluate(cell);
					columnValue = cellValue.getNumberValue();
					break;
				default:
					columnValue = cell.toString();
			}
		}
		return columnValue;
	}

}
